package com.cn.lyl.dao.impl;

import com.cn.lyl.dao.ServiceDao;
import com.cn.lyl.model.ServiceModel;
import com.cn.lyl.util.JDBC;
import com.cn.lyl.util.Tool;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ServiceDaoImpl implements ServiceDao {

    @Override
    public int add(ServiceModel serviceModel) {
        String sql ="INSERT INTO service (service_name, market_price, intro,flow,price,face,create_time, update_time, enable, remark) VALUES"+
                "('"+ Tool.stringIsNotNull(serviceModel.getServiceName())+"','"+Tool.floatIfNotNull(serviceModel.getMarketPrice())+"','"+
                Tool.stringIsNotNull(serviceModel.getIntro())+"','"+Tool.stringIsNotNull(serviceModel.getFlow())+"',"+Tool.floatIfNotNull(serviceModel.getPrice())+
                ",'"+Tool.stringIsNotNull(serviceModel.getFace())+"',now(),now(),1,'')";
        System.out.println("插入语句："+sql);
        return JDBC.update(sql);
    }


    @Override
    public int update(ServiceModel serviceModel) {
        String sql="update   service l  left join service_type_dependence d on l.id = d.service_id set ";
        StringBuffer stringBuffer = new StringBuffer();
        if(!"".equals(serviceModel.getServiceName())&&serviceModel.getServiceName()!=null) {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" l.service_name = '"+serviceModel.getServiceName()+"'");
        }
        if (serviceModel.getMarketPrice()!=null)
        {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" l.market_price = '"+serviceModel.getMarketPrice()+"'");
        }
        if (serviceModel.getPrice()!=null)
        {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" l.price = '"+serviceModel.getPrice()+"'");
        }
        if(!"".equals(serviceModel.getIntro())&&serviceModel.getIntro()!=null){
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" l.intro = '"+serviceModel.getIntro()+"'");
        }
        if(!"".equals(serviceModel.getFlow())&&serviceModel.getFlow()!=null){
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" l.flow = '"+serviceModel.getFlow()+"'");
        }
        if(!"".equals(serviceModel.getFace())&&serviceModel.getFace()!=null){
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" l.face = '"+serviceModel.getFace()+"'");
        }
        if (!"".equals(serviceModel.getDependenceObj())&&serviceModel.getDependenceObj()!=null){
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append("d.service_type_id = (select id from service_type where type = '"+serviceModel.getDependenceObj()+"')");
        }
        if(stringBuffer.length()>0){
            stringBuffer.append(", l.update_time = now()");
        }
        if(stringBuffer.length()>0){
            stringBuffer.append(",");
        }
        stringBuffer.append(" l.remark='' where l.id="+serviceModel.getId());
        sql=sql+stringBuffer.toString();
        System.out.println("修改："+sql);
        return JDBC.update(sql);

    }

    @Override
    public int delete(ServiceModel serviceModel) {
        String sql="delete from news where id ="+serviceModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public ServiceModel findById(int id) {
        String sql ="select l.id , l.service_name, l.market_price, l.intro,l.flow,l.price,l.face,r.type from service l  left join service_type_dependence d on l.id = d.service_id left join service_type r on r.id = d.service_type_id where l.id= "+id;
        System.out.println("查找"+sql);
        return setList(sql).size()>0?setList(sql).get(0):null;
    }

    @Override
    public List<ServiceModel> findAll(ServiceModel serviceModel) {
        String sql ="select l.id , l.service_name, l.market_price, l.intro,l.flow,l.price,l.face,r.type from service l  left join service_type_dependence d on l.id = d.service_id left join service_type r on r.id = d.service_type_id where 1=1 ";
        String stringSql = commonSql(serviceModel);
        int pageCurr=serviceModel.getPage().getPageCurr();
        int pageSize=serviceModel.getPage().getPageSize();
        stringSql +=" order by l.id desc limit "+(pageCurr-1)*pageSize+","+pageSize;
        sql = sql + stringSql;
        System.out.println(sql);
        return setList(sql);
    }

    @Override
    public ServiceModel findNewest() {
        String sql ="SELECT id from service order by id desc ";
        ServiceModel newsModel = new ServiceModel();
        ResultSet query = JDBC.query(sql);
        try{
            if(query.next()){
                newsModel.setId(query.getInt("id"));
            }
        }catch (Exception e){

        }
        return newsModel;
    }

    @Override
    public int countFindAll(ServiceModel serviceModel) {
        String sql ="select count(l.id) count from service l  left join service_type_dependence d on l.id = d.service_id left join service_type r on r.id = d.service_type_id where 1=1 ";
        String stringSql = commonSql(serviceModel);
        sql= sql+stringSql;
        ResultSet query = JDBC.query(sql);
        int size = 0;
        try {
            while (query.next()) {
                size = query.getInt("count");
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return size;
    }

    private List<ServiceModel> setList(String sql){
        ResultSet resultSet = JDBC.query(sql);
        List<String> colList = new ArrayList<>();
        try {
            ResultSetMetaData metaData = resultSet.getMetaData();
            for (int i = 1; i <=metaData.getColumnCount(); i++) {

                colList.add(metaData.getColumnLabel(i));

            }
        } catch (SQLException e) {
            return null;
        }
        List<ServiceModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                ServiceModel model = new ServiceModel();
                if (colList.contains("id")) {
                    model.setId(resultSet.getInt("id"));
                }
                if (colList.contains("service_name")) {
                    model.setServiceName(resultSet.getString("service_name"));
                }
                if (colList.contains("market_price")) {
                    model.setMarketPrice(resultSet.getFloat("market_price"));
                }
                if (colList.contains("price")) {
                    model.setPrice(resultSet.getFloat("price"));
                }
                if (colList.contains("intro")) {
                    model.setIntro(resultSet.getString("intro"));
                }
                if (colList.contains("flow")) {
                    model.setFlow(resultSet.getString("flow"));
                }
                if (colList.contains("face")) {
                    model.setFace(resultSet.getString("face"));
                }
                if (colList.contains("create_time")) {
                    model.setCreateTime(resultSet.getString("create_time"));
                }
                if (colList.contains("update_time")) {
                    model.setUpdateTime(resultSet.getString("update_time"));
                }
                if (colList.contains("enable")) {
                    model.setEnable(resultSet.getInt("enable"));
                }

                if (colList.contains("remark")) {
                    model.setRemark(resultSet.getString("remark"));
                }

                if(colList.contains("type")){
                    model.setDependenceObj(resultSet.getString("type"));
                }

                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }
    /*
    service_name, market_price,price
     */
    private String commonSql(ServiceModel serviceModel){
        StringBuffer stringBuffer = new StringBuffer();
        if (serviceModel.getServiceName() != null && !"".equals(serviceModel.getServiceName())) {
            stringBuffer.append(" and l.service_name like '%" + serviceModel.getServiceName() + "%'");
        }

        if ((serviceModel.getStart() != null && !"".equals(serviceModel.getStart())) && (serviceModel.getEnd() != null && !"".equals(serviceModel.getEnd()))) {
            stringBuffer.append(" and l.price between'" + serviceModel.getStart() + "' and '" + serviceModel.getEnd() + "'");
        }
        if ((serviceModel.getStart() != null && !"".equals(serviceModel.getStart())) && (serviceModel.getEnd() == null || "".equals(serviceModel.getEnd()))) {
            stringBuffer.append(" and l.price >='" +serviceModel.getStart() + "'");
        }
        if ((serviceModel.getStart() == null || "".equals(serviceModel.getStart())) && (serviceModel.getEnd() != null && !"".equals(serviceModel.getEnd()))) {
            stringBuffer.append(" and l.price <='" + serviceModel.getEnd() + "'");
        }



        if (serviceModel.getDependenceObj() != null && !"全部".equals(serviceModel.getDependenceObj() )&&!"".equals(serviceModel.getDependenceObj())) {
            stringBuffer.append(" and r.type='"+(String)serviceModel.getDependenceObj() +"'");
        }


        return stringBuffer.toString();
    }
}
